<!DOCTYPE html>
<html lang=zh-CN>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta property="og:description" content="伍一的笔记，Kool&#39;s Blog">
    <meta property="og:type" content="website">
    <meta name="description" content="伍一的笔记，Kool&#39;s Blog">
    <meta name="keyword"  content="伍一,伍一的博客">
    <link rel="shortcut icon" href="/img/favicon.ico">

    <title>
        
        Oracle学习笔记 - 伍一的笔记 | Kool&#39;s Blog
        
    </title>

    <!-- Custom CSS -->
    
<link rel="stylesheet" href="/css/aircloud.css">

    
<link rel="stylesheet" href="/css/gitment.css">

    <!--<link rel="stylesheet" href="https://imsun.github.io/gitment/style/default.css">-->
    <link href="//at.alicdn.com/t/font_620856_28hi1hpxx24.css" rel="stylesheet" type="text/css">
    <!-- ga & ba script hoook -->
    <script></script>

    







  
  
  
    <script type="text/javascript">
      var _hmt = _hmt || []
      ;(function () {
        var hm = document.createElement('script')
        hm.src = 'https://hm.baidu.com/hm.js?7e771422dc6f742629fa26747480f822'
        var s = document.getElementsByTagName('script')[0]
        s.parentNode.insertBefore(hm, s)
      })()
    </script>
  



<meta name="generator" content="Hexo 6.3.0"></head>

<body>

<div class="site-nav-toggle" id="site-nav-toggle">
    <button>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
    </button>
</div>

<div class="index-about">
    <i> 爱生活爱编码 </i>
</div>

<div class="index-container">
    
    <div class="index-left">
        
<div class="nav" id="nav">
    <div class="avatar-name">
        <div class="avatar radius">
            <img src="/img/zkool.png" />
        </div>
        <div class="name">
            <i>伍一</i>
        </div>
    </div>
    <div class="contents" id="nav-content">
        <ul>
            <li >
                <a href="/">
                    <i class="iconfont icon-shouye1"></i>
                    <span>主页</span>
                </a>
            </li>
            <li >
                <a href="/html/scanner/index.html">
                    <i class="iconfont icon-guidang2"></i>
                    <span>H5条码枪</span>
                </a>
            </li>
            <li >
                <a href="/html/udi/index.html">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>UDI</span>
                </a>
            </li>
            <li >
                <a href="/tags">
                    <i class="iconfont icon-biaoqian1"></i>
                    <span>标签</span>
                </a>
            </li>
            <li >
                <a href="/archives">
                    <i class="iconfont icon-guidang2"></i>
                    <span>存档</span>
                </a>
            </li>
            <li >
                <a href="/collect/">
                    <i class="iconfont icon-shoucang1"></i>
                    <span>收藏</span>
                </a>
            </li>
            <li >
                <a href="/about/">
                    <i class="iconfont icon-guanyu2"></i>
                    <span>关于</span>
                </a>
            </li>
            
            <li>
                <a id="search">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>搜索</span>
                </a>
            </li>
            
        </ul>
    </div>
    
        <div id="toc" class="toc-article">
    <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%94%A8%E6%88%B7%E8%A7%92%E8%89%B2"><span class="toc-text">用户角色</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%BF%9E%E6%8E%A5"><span class="toc-text">连接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%94%A8%E6%88%B7%E7%AE%A1%E7%90%86"><span class="toc-text">用户管理</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A7%92%E8%89%B2"><span class="toc-text">角色</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%AE%A1%E7%90%86%E6%9D%83%E9%99%90%E5%92%8C%E8%A7%92%E8%89%B2"><span class="toc-text">管理权限和角色</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E7%9A%84%E7%AE%A1%E7%90%86%EF%BC%9A"><span class="toc-text">表的管理：</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%AD%97%E6%AE%B5%E7%B1%BB%E5%9E%8B"><span class="toc-text">字段类型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A1%A8%E5%AD%97%E6%AE%B5%E7%AE%A1%E7%90%86"><span class="toc-text">表字段管理</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A1%A8%E8%AE%B0%E5%BD%95%E7%AE%A1%E7%90%86"><span class="toc-text">表记录管理</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%86%85%E7%BD%AE%E5%87%BD%E6%95%B0"><span class="toc-text">内置函数</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86"><span class="toc-text">数据库管理</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E9%80%BB%E8%BE%91%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D"><span class="toc-text">逻辑备份与恢复</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%AD%97%E5%85%B8"><span class="toc-text">数据字典</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A1%A8%E7%A9%BA%E9%97%B4%E4%BD%9C%E7%94%A8"><span class="toc-text">表空间作用</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E7%9A%84%E5%AE%8C%E6%95%B4%E6%80%A7"><span class="toc-text">数据的完整性</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%BA%A6%E6%9D%9F"><span class="toc-text">约束</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95"><span class="toc-text">索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-text">存储过程</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%87%BD%E6%95%B0"><span class="toc-text">函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-text">触发器</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#pl-sql%E7%9A%84%E8%BF%9B%E9%98%B6-%E6%8E%A7%E5%88%B6%E8%AF%AD%E5%8F%A5"><span class="toc-text">pl&#x2F;sql的进阶,控制语句</span></a></li></ol></li></ol>
</div>
    
</div>


<div class="search-field" id="search-field">
    <div class="search-bg" id="search-bg"></div>
    <div class="search-container">
        <div class="search-input">
            <span id="esc-search"> <i class="icon-fanhui iconfont"></i></span>
            <input id="search-input"/>
            <span id="begin-search">搜索</span>
        </div>
        <div class="search-result-container" id="search-result-container">

        </div>
    </div>
</div>

        <div class="index-about-mobile">
            <i> 爱生活爱编码 </i>
        </div>
    </div>
    
    <div class="index-middle">
        <!-- Main Content -->
        


<div class="post-container">
    <div class="post-title">
        Oracle学习笔记
    </div>

    <div class="post-meta">
        <span class="attr">发布于：<span>2011-10-19 20:04:20</span></span>
        
        <span class="attr">标签：/
        
        <a class="tag" href="/tags/#Oracle" title="Oracle">Oracle</a>
        <span>/</span>
        
        
        </span>
        <span class="attr">访问：<span id="busuanzi_value_page_pv"></span>
</span>
</span>
    </div>
    <div class="post-content ">
        <h3 id="用户角色"><a href="#用户角色" class="headerlink" title="用户角色"></a>用户角色</h3><h4 id="连接"><a href="#连接" class="headerlink" title="连接"></a>连接</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">conn[ect]  user/pass@服务 as sysdb/sysoper</span><br><span class="line">断开连接 disc</span><br><span class="line">修改密码自己passw</span><br><span class="line">start/@  d:aa.sql 执行脚本</span><br><span class="line">edit aa.sql 编辑文本</span><br><span class="line">spool  d:\bb.sql   spool off;  将屏幕上的内容写入d盘</span><br><span class="line">行宽set linesize</span><br><span class="line">行数set pagesize</span><br><span class="line">show user  显示当前用户</span><br><span class="line">desc  表名  ，查看表结构</span><br></pre></td></tr></table></figure>

<h4 id="用户管理"><a href="#用户管理" class="headerlink" title="用户管理"></a>用户管理</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">（管理员操作，不允许自杀）</span><br><span class="line">create user zhao identified by zhao;创建用户</span><br><span class="line">password zhao修改密码</span><br><span class="line">drop user zhao 【cascade】 删除用户,如果该用户已经建表则带参数</span><br><span class="line">grant  connect to zhao 赋权限  connect角色</span><br><span class="line">revoke</span><br><span class="line">权限：多个权限组成角色</span><br><span class="line">系统权限：用户对数据库的相关权限</span><br><span class="line">对象权限：用户对其它用户的数据对象操作的权限</span><br></pre></td></tr></table></figure>

<h4 id="角色"><a href="#角色" class="headerlink" title="角色"></a>角色</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">预定义角色：（自带的）connect,dba,resource(在任何一个表空间建表)</span><br><span class="line">自定义角色：</span><br><span class="line">口令管理：</span><br><span class="line">create profile aa limit failed-login-attempts 3 password-lock-time 2;用户只能尝试3次登陆，锁定时间为2天。</span><br><span class="line">alter user zhao profile aa;</span><br></pre></td></tr></table></figure>

<h4 id="管理权限和角色"><a href="#管理权限和角色" class="headerlink" title="管理权限和角色"></a>管理权限和角色</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line">所有角色信息select * from dba_roles;</span><br><span class="line">所有权限信息select * from system_privilege_map order by name;</span><br><span class="line">给用户授权限（系统权限）</span><br><span class="line">grant create session,create table to 用户名 with admin option--被授权的用户还可以将该权限传递</span><br><span class="line">grant create view to 用户名</span><br><span class="line">回收系统权限 （不是级联回收）</span><br><span class="line">revoke create session from 用户名</span><br><span class="line">对象权限：有alter修改表结构 delete select insert update 修改数据 index索引 references 引用 execute 执行</span><br><span class="line">授对象权：</span><br><span class="line">with grant option 选项不能被授予角色，被授权者可以将该权限传递给其他用户，</span><br><span class="line">grant select on emp to 用户名  --授查询权限</span><br><span class="line">grant update on emp to 用户名</span><br><span class="line">grant all on emp to 用户名</span><br><span class="line">grant update on emp(sal) to 用户名 --授予列权限</span><br><span class="line">grant select on emp(ename,sal) to 用户名 --只可查两个字段</span><br><span class="line">回收对象权限：（是级联回收）</span><br><span class="line">revoke select on emp from 用户名</span><br><span class="line">角色：预定义，事先定好的角色，自定义</span><br><span class="line">预定义角色:常用有connect ,resource,dba </span><br><span class="line">connect角色有系统权限：alter session,create cluster</span><br><span class="line">create database link,create session,create table,create view ,create sequence</span><br><span class="line">resource 角色：</span><br><span class="line">create cluster ,create indextype,create table,create sequence ,create type,create procedure,create trigger</span><br><span class="line">自定义角色:</span><br><span class="line">create role 角色名 not identified;--不验证</span><br><span class="line">create role 角色名　identified by  密码--数据库验证</span><br><span class="line">角色授权：和给用户授权没有太多的区别，系统权限的unlimited tablespace 和对象权限的with grant option选项是不能授予角色的。</span><br><span class="line">grant create session to 角色名 with admin option</span><br><span class="line">grant select on scott.emp to 角色名</span><br><span class="line">把角色给用户：grant 角色名 to 用户名 with admin option</span><br><span class="line">删除角色：（级联删除）</span><br><span class="line">drop role 角色名；</span><br><span class="line">查询角色信息</span><br><span class="line">select * from dba_roles;显示所有角色</span><br><span class="line">select privilege ,admin_option from role_sys_privs where role=&#x27;角色名&#x27;；--显示角色具有的系统权限</span><br><span class="line">select granted_role,default_role from dba_role_privs where grantee=&quot;用户名&quot;；--显示用户具有的角色，及默认角色</span><br></pre></td></tr></table></figure>

<h3 id="表的管理："><a href="#表的管理：" class="headerlink" title="表的管理："></a>表的管理：</h3><h4 id="字段类型"><a href="#字段类型" class="headerlink" title="字段类型"></a>字段类型</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">字符型</span><br><span class="line">char 最大2000，定长，char(10),相当于 ‘小韩      ’，后面用空格补全，效率快。</span><br><span class="line">varchar2(10) ，变长，最大4000字符。节省空间</span><br><span class="line">clob 最大4G</span><br><span class="line"></span><br><span class="line">数字型</span><br><span class="line">number 可表整数，也可表小数 number(5,2)  范围-999。99  999.99  五位，2位是小数。</span><br><span class="line"></span><br><span class="line">日期</span><br><span class="line">date  年月日和时分秒</span><br><span class="line">timestamp</span><br><span class="line"></span><br><span class="line">图片</span><br><span class="line">blob 图片/声音 4G</span><br></pre></td></tr></table></figure>

<h4 id="表字段管理"><a href="#表字段管理" class="headerlink" title="表字段管理"></a>表字段管理</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">清表drop table users;</span><br><span class="line">建表</span><br><span class="line">create table student(</span><br><span class="line">xh number(4),</span><br><span class="line">xm varchar2(20),</span><br><span class="line">sex char(2),</span><br><span class="line">birthday date,</span><br><span class="line">sal number(7,2)</span><br><span class="line">);</span><br><span class="line">create table classes(</span><br><span class="line">classId number(2),</span><br><span class="line">classname varchar(10)</span><br><span class="line">);</span><br><span class="line">添加一个字段</span><br><span class="line">alter table student add(classid number(2));</span><br><span class="line">修改字段的长度</span><br><span class="line">alter table student modify (xm varchar2(30));</span><br><span class="line">修改字段的类型或时名字</span><br><span class="line">alter table student modify (xm char(30));</span><br><span class="line">删除一个字段</span><br><span class="line">alter table student drop column sal;</span><br><span class="line">修改表的名字</span><br><span class="line">rename student to stu;</span><br><span class="line">删除表</span><br><span class="line">drop table student ;</span><br></pre></td></tr></table></figure>

<h4 id="表记录管理"><a href="#表记录管理" class="headerlink" title="表记录管理"></a>表记录管理</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line">添加数据</span><br><span class="line">insert into student values(1,&#x27;小赵&#x27;,&#x27;男&#x27;,&#x27;3-9月-11&#x27;,&#x27;999.99&#x27;,2);</span><br><span class="line">改日期的默认格式</span><br><span class="line">alter session set nls-date-format=&#x27;yyyy-mm-dd&#x27;</span><br><span class="line">查为有字段为空的记录</span><br><span class="line">select * from student where birthday is null</span><br><span class="line">is not null</span><br><span class="line">改</span><br><span class="line">update student set sex=&#x27;&#x27; where xh=&#x27;&#x27;</span><br><span class="line">删</span><br><span class="line">delete from student ;删数据；</span><br><span class="line">先savepoint aa 设置保存点。 删后  rollback to aa可以恢复</span><br><span class="line">drop from student ;删表结构和数据。</span><br><span class="line">truncate table student ;与delete相似，但无法恢复，速度快。不写日志。</span><br><span class="line">查询：</span><br><span class="line">取消重复 distinct</span><br><span class="line">set timing on;显示执行时间</span><br><span class="line">重复添加</span><br><span class="line">insert into users (userid,username) select * from user;</span><br><span class="line">排序</span><br><span class="line">order by desc/asc  asc默认从低到高  desc从高到低</span><br><span class="line">分组</span><br><span class="line">group by   having</span><br><span class="line">自连接：在同一张表的连接查询</span><br><span class="line">select </span><br><span class="line">from emp worker,emp boss </span><br><span class="line">where worker.mgr=boss.empno</span><br><span class="line">子查询：</span><br><span class="line">添加：行迁移  将emp表内容复制到zzz</span><br><span class="line">insert into zzz (myid ,myname,mydept) select empno,ename,deptno from emp</span><br><span class="line">更新:scott的岗位，工资，补助与smith一样</span><br><span class="line">update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=&#x27;SMITH&#x27;) where ename=&#x27;SCOTT&#x27;</span><br><span class="line">事物：</span><br><span class="line">例如，银行账户转账，一边减，一边加，是同一件事，同时执行。</span><br><span class="line">只读事物:如某个时间点之前的统计</span><br><span class="line">set transaction read only</span><br></pre></td></tr></table></figure>

<h4 id="内置函数"><a href="#内置函数" class="headerlink" title="内置函数"></a>内置函数</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">字符函数</span><br><span class="line">nvl(aa,&#x27;&#x27;),如果aa为null，则用返回后面的值。</span><br><span class="line">lower();将字符串转为小写</span><br><span class="line">upper();为大写</span><br><span class="line">length();字符串长度</span><br><span class="line">substr(char,m,n);取子串m从开始 n取几个</span><br><span class="line">数学函数</span><br><span class="line">round(n,[m])四舍五入 m 保留小数位位数</span><br><span class="line">trunc(n,[m]) 截取数字 m小数位，无m截取整数</span><br><span class="line">mod（m,n） 取模m/n</span><br><span class="line">floor(n) 小于或等于n的最大整数</span><br><span class="line">ceil(n) 大于或等于n的最小整数</span><br><span class="line"></span><br><span class="line">日期函数</span><br><span class="line">sysdate</span><br><span class="line">add_months(d,n) d+n个月</span><br><span class="line">last_day(d) 指定日期所在月份的最后一天</span><br><span class="line"></span><br><span class="line">转换函数</span><br><span class="line">to_char(&#x27;&#x27;,&#x27;l9999.99&#x27;) l表本地货币符号 c国际</span><br><span class="line">to_date</span><br><span class="line"></span><br><span class="line">系统函数</span><br><span class="line">sys_context(&#x27;userenv&#x27;,&#x27;db_name&#x27;)</span><br><span class="line">db_name 库名</span><br><span class="line">language 语言</span><br><span class="line">session_user 当前会话客户所对应的库用户名</span><br><span class="line">nls_date_format 日期格式</span><br><span class="line">current_schema 方案名</span><br></pre></td></tr></table></figure>

<h3 id="数据库管理"><a href="#数据库管理" class="headerlink" title="数据库管理"></a>数据库管理</h3><p>show parameter 显示初始化参数</p>
<h4 id="逻辑备份与恢复"><a href="#逻辑备份与恢复" class="headerlink" title="逻辑备份与恢复"></a>逻辑备份与恢复</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">导出：3种  exp命令</span><br><span class="line">导出表</span><br><span class="line">导出自个表 exp  tables=(表名1，表2)  file=d:\e1.dmp</span><br><span class="line">direct=y 直接导出，数据量大时用</span><br><span class="line">导出其它方案的表:需要dba权限或exp_full_database权限</span><br><span class="line">system就可以导出scott的表 exp  tables=(scott.emp) file=d:\e2.dmp</span><br><span class="line">导出表结构</span><br><span class="line">exp  tables=(emp) file=d:\e3.dop rows=n</span><br><span class="line">导出方案</span><br><span class="line">导出自己的方案</span><br><span class="line">exp  owner=scott file=d:\scott.dmp</span><br><span class="line">导出其它方案  需要dba权限或exp_full_database权限</span><br><span class="line">exp  owner=(system,scott) 多个file=d:\system.dmp</span><br><span class="line">导出数据库  需dba权限或exp_full_database</span><br><span class="line">exp  full=y inctype=complete file=d:\x.dmp</span><br><span class="line">导入</span><br><span class="line">导入自己表</span><br><span class="line">imp  tables=(emp) file=d:\1.dmp</span><br><span class="line">导入其它用户</span><br><span class="line">imp  tables=(emp) file=d:\2.dmp touser=scott</span><br><span class="line">导入表结构</span><br><span class="line">imp  tables=(emp) file=d:\3.dmp</span><br><span class="line"> rows=n</span><br><span class="line">导入数据，表已经存在，只倒数据</span><br><span class="line">imp  tables=(emp) file=d:\4.dmp  ignore=y</span><br><span class="line">导入方案</span><br><span class="line">导入自己 imp  file=d:\</span><br><span class="line">导入其它方案 要求有dba权限</span><br><span class="line">imp userid=system/manager file=d:\a.dmp fromuser=system touser=scott</span><br><span class="line">导入数据库</span><br><span class="line">imp userid=system/manager full=y file=</span><br></pre></td></tr></table></figure>

<h4 id="数据字典"><a href="#数据字典" class="headerlink" title="数据字典"></a>数据字典</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">查当前用户所拥有的所有表user_tables</span><br><span class="line">select table_name from user_tables;</span><br><span class="line">当前用户可以访问的所有表all_tables</span><br><span class="line">select table_name from all_tables;</span><br><span class="line">所有方案拥有的数据库表，需dba角色或select any table系统权限  dba_tables</span><br><span class="line">select table_name from dba_tables;</span><br><span class="line">dba_users显示所有数据库用户的详细信息</span><br><span class="line">select username from dba_users</span><br><span class="line">数据字典视图dba_sys_privs，可以显示用户所具有的系统权限</span><br><span class="line">dba_tab_privs 用户具有的对象权限</span><br><span class="line">dba_col_privs 用户具有的列权限</span><br><span class="line">dba_role_privs用户具有的角色</span><br><span class="line">查询oracle多少种角色select * from dba_roles;</span><br><span class="line">一个角色有多少个权限（系统权限，对象权限）</span><br><span class="line">系统权限</span><br><span class="line">select * from dba_sys_privs where grantee=&#x27;CONNECT&#x27;</span><br><span class="line">select * from role_sys_privs wher role=&#x27;DBA&#x27;</span><br><span class="line">对象权限</span><br><span class="line">select * from dba_tab_privs where grantee=&#x27;CONNECT&#x27;</span><br><span class="line">查oracle中所有的系统权限</span><br><span class="line">select * from system_privilege_map order by name;</span><br><span class="line">查所有对象权限</span><br><span class="line">select distinct privilege from dba_tab_privs;</span><br><span class="line">某个用户具有的角色</span><br><span class="line">select * from dba_role_privs where grantee=&#x27;用户名&#x27;</span><br><span class="line">显示当前用户可以访问的所有数据字典视图</span><br><span class="line">select * from dict where comments like &#x27;%grant%&#x27;;</span><br><span class="line">显示当前数据库的全称</span><br><span class="line">select * from global_name;</span><br></pre></td></tr></table></figure>

<h4 id="表空间作用"><a href="#表空间作用" class="headerlink" title="表空间作用"></a>表空间作用</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line">控制数据库占用的磁盘空间</span><br><span class="line">dba可以将不同数据类型部署到不同的位置，有利于提高i/o性能，同时利于备份和恢复等管理操作</span><br><span class="line">创建表空间create tablespace 名字 datafile &#x27;d:\data01.dbf&#x27;</span><br><span class="line">size 20m uniform size 128k</span><br><span class="line">改变表空间的状态</span><br><span class="line">使表空间脱机</span><br><span class="line">alter tablespace 表空间名 offline;</span><br><span class="line">使表空间联机</span><br><span class="line">alter tablespace 表空间名 online;</span><br><span class="line">只读表空间</span><br><span class="line">alter tablespace 表空间名read only/write</span><br><span class="line">知道表空间名，显示该表空间包含的所有表</span><br><span class="line">select  * from all_tables where tablespace_name=&#x27;表空间名&#x27;</span><br><span class="line">知道表名，查看该表属于哪个表空间</span><br><span class="line">select tablespace_name ,table_name from user_tables where table_name=&#x27;emp&#x27;</span><br><span class="line">删除表空间,同时删除该空间的所有数据库对象，datafilse表示将数据库文件也删除</span><br><span class="line">drop tablespace &#x27;表空间&#x27; including contents and datafiles;</span><br><span class="line">扩展表空间</span><br><span class="line">1.增加数据文件</span><br><span class="line">alter tablespace 表空间名 add datafile &#x27;d:\test.dbf&#x27; size 20m</span><br><span class="line">2 增加数据文件的大小</span><br><span class="line">alter tablespace 表空间名 &#x27;d:test.dbf&#x27; resize 20m</span><br><span class="line">3 设置文件的自动增长</span><br><span class="line">alter tablespace 表空间名 &#x27;d:\test.dbf&#x27; autoextend on next 10m maxsize 500m</span><br><span class="line">移动数据文件</span><br><span class="line">1 确定数据文件所在的表空间</span><br><span class="line">select tablespace_name from dba_data_files where file_name=&#x27;d:\test.dbf&#x27;;</span><br><span class="line">2 使表空间脱机 确保数据文件的一致性，将表空间转变为offline状态</span><br><span class="line">alter tablespace 表空间 offline;</span><br><span class="line">3 使用命令移动数据文件到指定的目标位置</span><br><span class="line">host move d:\test.dbf  c:\test.dbf</span><br><span class="line">4 物理上移动了数据文件后，还必须执行alter tablespace命令对数据库文件进行逻辑修改</span><br><span class="line">alter tablespace 表空间名 rename datafile &#x27;d:\test.dbf&#x27; to &#x27;c:\test.dbf&#x27;</span><br><span class="line">5 使表空间联机</span><br><span class="line">alter tablespace 表空间 online;</span><br></pre></td></tr></table></figure>

<h3 id="数据的完整性"><a href="#数据的完整性" class="headerlink" title="数据的完整性"></a>数据的完整性</h3><p>可以使用约束，触发器，应用程序（过程，函数）三种方法来实现。</p>
<h4 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line">用于确保数据库数据满足特定的商业规则</span><br><span class="line">包括：not null, unique唯一,可以为null,primary key不能重复不能为null,foreign key,check检查</span><br><span class="line">例子：</span><br><span class="line">create table goods (--商品表</span><br><span class="line">goodsId char(8) primary key,--主键，定长字符8位</span><br><span class="line">goodsName varchar2(30),</span><br><span class="line">unitprice number(10,2) check (unitprice&gt;0),--单价大于0</span><br><span class="line">category varchar2(30),</span><br><span class="line">provider varchar2(30)</span><br><span class="line">);</span><br><span class="line">create table customer(--客户表</span><br><span class="line">customerId char(8) primary key,</span><br><span class="line">name varchar2（30） not null,--不为空</span><br><span class="line">address varchar2(50),</span><br><span class="line">email varchar2(50) unique,--不能重复</span><br><span class="line">sex char(2) default &#x27;男&#x27; check (sex in(&#x27;男&#x27;,&#x27;女&#x27;)),--默认是男</span><br><span class="line">cardId char(18)</span><br><span class="line">);</span><br><span class="line">create table purchase(</span><br><span class="line">customerId char(8)  references customer(customerId),</span><br><span class="line">goodsId char(8) references goods(goodsId),</span><br><span class="line">nums number(5) check(nums between 1 and 30)--数量必须是1到30之间</span><br><span class="line">)</span><br><span class="line">添加约束  加notnull用modify 加其它约束用add</span><br><span class="line">alter table goods modify goodsName not null;</span><br><span class="line">alter table customer add constraint 名字 unique(cardid);--省份证也不重复</span><br><span class="line">alter table customer add constraint addresscheck(约束名) check  (address in (&#x27;东城&#x27;,&#x27;海淀&#x27;,&#x27;朝阳&#x27;,&#x27;西城&#x27;))</span><br><span class="line">删除约束</span><br><span class="line">alter table 表名 drop constraint 约束名称；</span><br><span class="line">删除主键约束时，删除主表的主键是必须带上cascade</span><br><span class="line">alter table 表名 drop primary key cascade;</span><br><span class="line">显示约束信息</span><br><span class="line">通过查询数据字典视图user_constranints</span><br><span class="line">select constraint_name,constraint_type,status,validated from</span><br><span class="line">user_constraints where table_name=&#x27;表名&#x27;；</span><br><span class="line">显示约束列</span><br><span class="line">select column_name,position from user_cons_columns where constraint_name=&#x27;约束名&#x27;;</span><br><span class="line">列级定义：定义列的同时定义约束</span><br><span class="line">create table department(</span><br><span class="line">dept_id number(2) constraint pk_deparment primary key,</span><br><span class="line">name varchar2(2),</span><br><span class="line">loc varchar2(12)</span><br><span class="line">);</span><br><span class="line">表级定义：定义了所有列后，在定义约束</span><br><span class="line">create table employee(</span><br><span class="line">emp_id number(4),</span><br><span class="line">name varchar2(15),</span><br><span class="line">dept_id number(2),</span><br><span class="line">constraint pk_employee primary key(emp_id),</span><br><span class="line">constraint fk_department foreign key(dept_id) references department(dept_id);</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<h4 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">单列索引</span><br><span class="line">create index 索引名 on 表名（列名）；</span><br><span class="line">复合索引</span><br><span class="line"> create index 索引名 on emp(ename,job); </span><br><span class="line">使用原则：</span><br><span class="line">1 在大表上建立索引才有意义</span><br><span class="line">2 在where子句或是连接条件上经常引用的列上建立索引</span><br><span class="line">3 索引的层次不要超过4层</span><br><span class="line">索引缺点：</span><br><span class="line">1 建立索引，系统要占用大约为表的1.2倍的硬盘盒内存空间来保存索引</span><br><span class="line">2 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性；</span><br><span class="line">查询索引信息</span><br><span class="line">显示表的所有索引</span><br><span class="line">select index_name,index_type from user_indexs where table_name=&#x27;表名&#x27;;</span><br><span class="line">显示索引列</span><br><span class="line">select table_name,column_name from user_ind_columns where idex_name=&#x27;列名&#x27;</span><br></pre></td></tr></table></figure>

<h4 id="存储过程"><a href="#存储过程" class="headerlink" title="存储过程"></a>存储过程</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br></pre></td><td class="code"><pre><span class="line">pl/sql  procedural language /sql 过程化语言</span><br><span class="line">pl/sql编程技术：存储过程，函数，触发器……</span><br><span class="line">创建:</span><br><span class="line">create or replace procedure 名 is </span><br><span class="line">begin </span><br><span class="line">insert into mytest values(&#x27;zhao&#x27;,&#x27;123&#x27;);</span><br><span class="line">end;</span><br><span class="line">显示错误：show error</span><br><span class="line">调用过程：1.exec 过程名（参数1，参数2）</span><br><span class="line">         2. call 过程名（参数1，参数2）</span><br><span class="line"></span><br><span class="line">pl/sql块结构:</span><br><span class="line"></span><br><span class="line">编写规范：</span><br><span class="line">注释：--单行注释</span><br><span class="line">/**/多行注释</span><br><span class="line"></span><br><span class="line">标识符命名规范</span><br><span class="line">变量v_为前缀</span><br><span class="line">常量c_为前缀</span><br><span class="line">游标_cursor为后缀</span><br><span class="line">例外e_为前缀</span><br><span class="line">declear定义部分，可选</span><br><span class="line">begin 执行部分</span><br><span class="line">exception 例外处理部分</span><br><span class="line">end;</span><br><span class="line"></span><br><span class="line">实例1  </span><br><span class="line">set serveroutput on/off 打开/关闭输出选项</span><br><span class="line">begin </span><br><span class="line">   dbms_output.put_line(&#x27;hello&#x27;);</span><br><span class="line">end;</span><br><span class="line"></span><br><span class="line">实例2</span><br><span class="line">declare </span><br><span class="line">    v_ename varchar2(5);--定义字符串变量</span><br><span class="line">    v_sal number(7,2);</span><br><span class="line">begin </span><br><span class="line">   select ename,sal into v_ename,v_sal from emp where      empno=&amp;no;--把查出的ename 赋给v_name,&amp;表示要接受从控制台输入的变量</span><br><span class="line">   dbms_output.put_line(&#x27;雇员名：&#x27;||v_ename||&#x27;工资：&#x27;||v_sal);</span><br><span class="line">exception--异常处理</span><br><span class="line">when no_data_found then --预定义的例外异常</span><br><span class="line">dbms_output.put_line(&#x27;输入编号有误！&#x27;);</span><br><span class="line">end;</span><br><span class="line"></span><br><span class="line">过程：</span><br><span class="line">例1：可以输入雇员名，新工资，可修改雇员的工资</span><br><span class="line">create procedure pro001 (name varchar2,newsal number) is</span><br><span class="line">begin--执行部分,根据用户名修改工资</span><br><span class="line">update emp set sal=newsal where ename=name;</span><br><span class="line">end;</span><br><span class="line"></span><br><span class="line">调用过程：exec pro001(&#x27;SCOTT&#x27;,&#x27;5000&#x27;);</span><br><span class="line"></span><br><span class="line">java调用过程：</span><br><span class="line">Connection ct=DriverManager.getConnection(&#x27;url&#x27;,&#x27;user&#x27;,&#x27;password&#x27;);</span><br><span class="line">CallableStatement cs=ct.prepareCall(&quot;&#123;call pro001(?,?)&#125;&quot;);</span><br><span class="line">cs.setString (1,&quot;SCOTT&quot;);</span><br><span class="line">cs.setInt(2,100);</span><br></pre></td></tr></table></figure>

<h4 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">输入雇员的姓名，返回该雇员的年薪</span><br><span class="line">create function fun001(name varchar) return number is yearSal number(7,2);</span><br><span class="line">begin --执行</span><br><span class="line">select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=name;</span><br><span class="line">return yearSal;</span><br><span class="line">end;</span><br><span class="line">显示错误：show error;</span><br><span class="line">调用函数：var aaa number;--先定义一个变量</span><br><span class="line">call fun001(&#x27;SCOTT&#x27;) into:aaa;</span><br><span class="line">java调用：select fun001(&#x27;scott&#x27;) from dual;</span><br><span class="line">             用rs.getInt(1)得到返回值。</span><br><span class="line"></span><br><span class="line">包：用于在逻辑上组合过程和函数，它由包规范和包体两部分组成</span><br><span class="line">create package pack001 is--创建一个包，声明该包有一个过程，一个函数</span><br><span class="line">    procedure pro001(name varchar2,newsal number);</span><br><span class="line">    function fun001(name varchar2) return number;</span><br><span class="line">end;</span><br><span class="line">包体：包的规范只包含了过程和函数的声明，没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。</span><br><span class="line">create or replace package body pack001 is--给包实001现包体</span><br><span class="line">   procedure pro001(name varchar2,newsal number) is</span><br><span class="line">begin</span><br><span class="line">   update emp set sal=newsal where ename=name;</span><br><span class="line">end;</span><br><span class="line">function fun001(name varchar2) return number is</span><br><span class="line">yearsal number;</span><br><span class="line">begin</span><br><span class="line">   select sal*12+nvl(comm,0) into yearsal from emp where ename=name;</span><br><span class="line">return yearsal;</span><br><span class="line">end;</span><br><span class="line">end;</span><br><span class="line">调用包：exec pack001.pro001(&#x27;scott&#x27;,&#x27;100&#x27;);</span><br></pre></td></tr></table></figure>

<h4 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br></pre></td><td class="code"><pre><span class="line">隐含执行的存储过程。定义触发器是，必须要指定触发的事件和触发的操作，常用的触发事件包括：insert,update,delete</span><br><span class="line">变量：</span><br><span class="line">标量类型scalar</span><br><span class="line">  定义一个变长字符串 v_ename varchar2(10);</span><br><span class="line">  定义一个小数  v_sal number(6,2);</span><br><span class="line">  定义一个小数并给一个初始值为5.4  ：=是pl/sql的赋值号</span><br><span class="line">  v_sal2 number(6,2):=5.4</span><br><span class="line">  定义一个日期类型的数据 v_hiredate date;</span><br><span class="line">  定义一个布尔变量，不能为空，初始值为false</span><br><span class="line"> v_valid boolean not null default false;</span><br><span class="line">复合类型composite</span><br><span class="line">参照类型reference</span><br><span class="line">lob (large object)</span><br><span class="line">例子:输入员工号，显示雇员名，工资，个人所得税，税率为0.03,。</span><br><span class="line">declare</span><br><span class="line">c_tax_rate number(3,2):=0.03;--常量</span><br><span class="line">v_ename varchar2(5);--用户名，变量</span><br><span class="line">v_sal number(7,2);</span><br><span class="line">v_tax_sa number(7,2);--所得税</span><br><span class="line">begin --执行</span><br><span class="line">select ename,sal into v_ename,v_sal from emp where empno=&amp;no;</span><br><span class="line">--计算所得税</span><br><span class="line">v_tax_sal:=v_sal*c_tax_rate;</span><br><span class="line">--输出</span><br><span class="line">dbms_output.put_line(&#x27;&#x27;||v_name||&#x27;&#x27;||v_sal||&#x27;&#x27;||v_tax_sal);</span><br><span class="line">end;</span><br><span class="line"></span><br><span class="line">%type类型，属标量</span><br><span class="line">v_ename emp.ename%type表示v_eanme 的类型与emp中ename大小一致</span><br><span class="line">复合变量：存放多个值</span><br><span class="line">pl/sql记录，类似于高级语言的类，结构体</span><br><span class="line">--定义一个pl/sql记录类型 em_record_type</span><br><span class="line">declare</span><br><span class="line">type em_record_type is record(name emp.ename%type,</span><br><span class="line">salary emp.sal%type,title emp.job%type);</span><br><span class="line">--定义一个zk_record 变量，这个变量的类型em_record_type</span><br><span class="line">zk_record em_record_type;</span><br><span class="line">begin</span><br><span class="line">select ename,sal,job into zk_record from emp where empno=7788;</span><br><span class="line">dbms_output.put_line(zk_record.name);</span><br><span class="line">end;</span><br><span class="line">pl/sql表:相当于高级语言中的数组，下标可以为负数，</span><br><span class="line">declare--定义一个表类型，zk_table_type，用于存放emp.ename%type数组，index by  binary_integer表示下标是整数</span><br><span class="line">type zk_table_type is table of emp.ename%type index by  binary_integer;</span><br><span class="line">--定义了一个变量zk_table，类型是zk_table_type</span><br><span class="line">zk_table zk_table_type；</span><br><span class="line">begin</span><br><span class="line">select ename into zk_table(0) from emp where empno=7788;--把ename放到下标为0的位置</span><br><span class="line">dbms_output.put_line(zk_table(0));</span><br><span class="line">end;</span><br><span class="line">参照变量</span><br><span class="line">如果把上面select语句的where之后去掉，则用参照变量</span><br><span class="line">游标变量</span><br><span class="line">例子：</span><br><span class="line">输入部门号，显示该部门的所有员工名和工资</span><br><span class="line">declare</span><br><span class="line">--定义游标类型</span><br><span class="line">type zk_emp_cursor is ref cursor;</span><br><span class="line">--再定义一个游标变量test_cursor</span><br><span class="line">test_cursor zk_emp_cursor;</span><br><span class="line">--定义变量</span><br><span class="line">v_name emp.ename%type;</span><br><span class="line">v_sal emp.sal%type;</span><br><span class="line">begin</span><br><span class="line">--把test_cursor和一个select 结和</span><br><span class="line">open test_cursor for select ename ,sal from  emp where deptno=&amp;no;</span><br><span class="line">--循环取出</span><br><span class="line">loop</span><br><span class="line">   fetch test_cursor into v_ename,v_sal;</span><br><span class="line">--判断工资高低，决定是否更新</span><br><span class="line">if</span><br><span class="line">--退出条件，判断是否test_cursor为空</span><br><span class="line">  exit when test_cursor%notfound;</span><br><span class="line">   dbms_outpub.put_line(&#x27;&#x27;||v_ename||&#x27;&#x27;||v_sal);</span><br><span class="line">end loop;</span><br><span class="line">--关闭游标</span><br><span class="line">close test_cursor;</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>

<h4 id="pl-sql的进阶-控制语句"><a href="#pl-sql的进阶-控制语句" class="headerlink" title="pl&#x2F;sql的进阶,控制语句"></a>pl&#x2F;sql的进阶,控制语句</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br></pre></td><td class="code"><pre><span class="line">条件分支语句</span><br><span class="line">if--then,</span><br><span class="line">if--then--else,二重条件分支</span><br><span class="line">if--then--elsif--else多重条件分支</span><br><span class="line">例：写一个过程，可以输入一个雇员名，该员工工资低于2000就增加10%</span><br><span class="line">create or replace procedure zk_pro01(zkName varchar2) is</span><br><span class="line">v_sal emp.sal%type;</span><br><span class="line">begin</span><br><span class="line">select sal into v_sal form emp where ename=zkName;</span><br><span class="line">if v_sal&lt;2000 then </span><br><span class="line">update emp set sal=sal+sal*10% where ename=zkName;</span><br><span class="line">end;/</span><br><span class="line">调用： exec zk_pro01(&#x27;SCOTT&#x27;);</span><br><span class="line">例子：如果该员工的补助不是0，就加100，如果补助为0，就加200；</span><br><span class="line">create or replace procedure zk_pro01(zkName varchar2) is</span><br><span class="line">v_comm emp.comm%type;</span><br><span class="line">begin</span><br><span class="line">select comm into v_comm form emp where ename=zkName;</span><br><span class="line">if v_comm&lt;&gt;0 then </span><br><span class="line">update emp set comm=comm+100 where ename=zkName;</span><br><span class="line">else </span><br><span class="line">update emp set comm=comm+200 where ename=zkName;</span><br><span class="line">end if;</span><br><span class="line">end;/</span><br><span class="line">列子：</span><br><span class="line">输入员工号，如果该员工是president 就工资加1000，如果是manager就加500，其它加200</span><br><span class="line">create or replace procedure zk_pro01(zkName number) is</span><br><span class="line">v_job emp.job%type;</span><br><span class="line">begin</span><br><span class="line">select job into v_job form emp where empno=zkName;</span><br><span class="line">if v_job=&#x27;president&#x27; then </span><br><span class="line">update emp set sal=sal+1000 where empno=zkName;</span><br><span class="line">elsif v_job=&#x27;manager&#x27; then </span><br><span class="line">update emp set sal=sal+500 where empno=zkName;</span><br><span class="line">else</span><br><span class="line">update emp set sal=sal+200 where empno=zkName;</span><br><span class="line">end if;</span><br><span class="line">end;/</span><br><span class="line">循环语句 -loop--end loop 至少执行一次</span><br><span class="line">例子：写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加</span><br><span class="line">create or replace procedure zk_pro02(zkName varchar2) is</span><br><span class="line">v_num number:=1; </span><br><span class="line">begin</span><br><span class="line"> loop</span><br><span class="line">   insert into users values(v_num,zName);</span><br><span class="line">  --判断是否要退出循环</span><br><span class="line">exit  when v_num=10;</span><br><span class="line">--自增</span><br><span class="line">v_num:=v_num+1;</span><br><span class="line">end loop;</span><br><span class="line">end;</span><br><span class="line">while循环:</span><br><span class="line">例子：写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加</span><br><span class="line">create or replace procedure zk_pro02(zkName varchar2) is</span><br><span class="line">v_num number:=11; </span><br><span class="line">begin</span><br><span class="line">while v_num&lt;=20 loop</span><br><span class="line">  insert into users values(v_num,zkName);</span><br><span class="line">  v_num:=v_num+1;</span><br><span class="line">end loop;</span><br><span class="line">end;</span><br><span class="line">for循环</span><br><span class="line">begin </span><br><span class="line">   for i in reverse 1..10 loop</span><br><span class="line">   insert into users values(i,&#x27;zzz&#x27;);</span><br><span class="line">  end loop;</span><br><span class="line">end;</span><br><span class="line">顺序控制语句，goto ,null</span><br><span class="line">多层循环中，直接跳出最外边，可用goto,一般不用</span><br><span class="line">goto lable  lable是已经定义好的标号名</span><br><span class="line">列子</span><br><span class="line">declare </span><br><span class="line">i int:=1; --int表示整数</span><br><span class="line">begin </span><br><span class="line"> loop</span><br><span class="line">   dbms_output.put_line(&#x27;&#x27;||i);</span><br><span class="line"> if i=10 then</span><br><span class="line">  goto end_loop;</span><br><span class="line">  end if;</span><br><span class="line"> i:=i+1;</span><br><span class="line">  end loop;</span><br><span class="line"> &lt;&lt;end_loop&gt;&gt;--标号</span><br><span class="line"> dbms_output.put_line(&#x27;循环结束&#x27;);</span><br><span class="line">end;/</span><br><span class="line">null 语句不会执行任何操作，会直接将控制传递到下一条语句</span><br><span class="line"></span><br><span class="line">分页过程：</span><br><span class="line">写一个过程，可以想book中添加数据，无返回值</span><br><span class="line">--book表</span><br><span class="line">create table book(bookid number,bookName varchar2(50),publishHouse varchar2(50));</span><br><span class="line">--编写过程</span><br><span class="line">create or replace procedure zk_pro03(zkBookId  in number,zkBookName in varchar2,zkpub in varchar2)--in 表示输入变量，不写默认有in。out表示一个输出参数</span><br><span class="line">is </span><br><span class="line">begin </span><br><span class="line">inset into book values(zkBookId,zkBookName,zkpub);</span><br><span class="line">end;</span><br><span class="line">例子：有返回值的存储过程</span><br><span class="line">可输入员工编号，返回该员工的姓名</span><br><span class="line">create or repalce procedure zk_pro04(no in number,name out varchar2) is</span><br><span class="line">begin</span><br><span class="line">select ename into name from where empno=no;</span><br><span class="line">end;</span><br><span class="line">java调用CallableStatement cs =ct.prepareCall(&quot;&#123;call zk_pro(?,?)&#125;&quot;);</span><br><span class="line">cs.setInt(1,7788);</span><br><span class="line">cs.registerOutParameter(2,oracle.jdbc.OrcleType.VARCHAR);</span><br><span class="line">cs.execute();</span><br><span class="line">String name=cs.getString(2);//取出返回值</span><br><span class="line">System.out.println(&quot;&quot;+name);</span><br><span class="line">返回多个字段</span><br><span class="line">create or repalce procedure zk_pro04(no in number,name out varchar2，zksal out number,zkjob out varchar2) is</span><br><span class="line">begin</span><br><span class="line">select ename,sal,job into name,zksal,zkjob from where empno=no;</span><br><span class="line">end;</span><br><span class="line">返回结果集的过程</span><br><span class="line">--先创建一个包,定义了一个游标类型，test_cursor</span><br><span class="line">create or replace package pack001 as typ test_cursor is ref cursor;</span><br><span class="line">end pack001;</span><br><span class="line">--建存储过程</span><br><span class="line">create or replace procedure pro001(zkdeptno in number,p_cursor out pack001.test_cursor) is</span><br><span class="line">begin </span><br><span class="line"> open p_cursor for select * from emp where deptno=zkdetpnao;</span><br><span class="line">end;</span><br><span class="line">--在java中调用</span><br><span class="line">java调用CallableStatement cs =ct.prepareCall(&quot;&#123;call pro001(?,?)&#125;&quot;);</span><br><span class="line">cs.setInt(1,7788);</span><br><span class="line">cs.registerOutParameter(2,oracle.jdbc.OrcleType.CURSOR);</span><br><span class="line">cs.execute();</span><br><span class="line">ResultSet rs=(ResultSet)cs.getObject(2);</span><br><span class="line">while(rs.next)&#123;</span><br><span class="line">    System.out.println(&quot;&quot;+rs.getInt(1)+&quot;&quot;+rs.getString(2));</span><br><span class="line">&#125;</span><br><span class="line">分页：</span><br><span class="line">select * from--分页语句</span><br><span class="line">(select t1.* rownum rn from (select * from emp) t1 where rownum&lt;=10)</span><br><span class="line">where rn&gt;=6;</span><br><span class="line">--先建一个包,定义游标类型</span><br><span class="line">create or replace package testpackage as type test_cursor is ref cursor;</span><br><span class="line">end testpackage;</span><br><span class="line">create or replace procedure fenye(tablename in varchar2,pagesize in number,--一页显示大小</span><br><span class="line">pagenow in number,--当前页数</span><br><span class="line">rowscount out number,--总记录数</span><br><span class="line">pagescount out number，--总页数</span><br><span class="line">p_cursor out testpackage.test_cursor--返回记录集) is</span><br><span class="line">--定义sql语句 字符串</span><br><span class="line">v_sql varchar2(1000);</span><br><span class="line">--定义两个整数</span><br><span class="line">v_begin number:=(pagenow-1)*pagesize+1;</span><br><span class="line">v_end number:=pagenow*pagesize; </span><br><span class="line">begin</span><br><span class="line">v_sql:=&#x27;select * from</span><br><span class="line">(select t1.* rownum rn from (select * from &#x27;||tablename||&#x27; order by sal) t1 where rownum&lt;=&#x27;||v_end||&#x27;)</span><br><span class="line">where rn&gt;=&#x27;||v_begin;</span><br><span class="line">--把游标和sql关联</span><br><span class="line">open p_cursor for v_sql;</span><br><span class="line">--计算总页数，总记录数</span><br><span class="line">v_sql:=&#x27;select count(*) from &#x27;||tablename;</span><br><span class="line">--执行sql，并把返回值赋给rowscount</span><br><span class="line">execute immediate v_sql into rowscount;</span><br><span class="line">--计算pagescount </span><br><span class="line">if mod(rowscount,pagesize)=0 then</span><br><span class="line">pagescount:=rowscount/pagesize;</span><br><span class="line">else</span><br><span class="line">pagescount:=rowscount/pagesize+1;</span><br><span class="line">end if;</span><br><span class="line">--关闭游标</span><br><span class="line">close p_cursor;</span><br><span class="line">end;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>原始记录地址： <a target="_blank" rel="noopener" href="http://wenzhang.baidu.com/page/view?key=98e75c20ad5b0e14-1426452139">http://wenzhang.baidu.com/page/view?key=98e75c20ad5b0e14-1426452139</a></p>
</blockquote>

        
            <div class="donate-container">
    <div class="donate-button">
        <button id="donate-button">赞赏</button>
    </div>
    <div class="donate-img-container hide" id="donate-img-container">
        <img id="donate-img" src="" data-src="/img/my_wechat.jpg">
        <p> 感谢鼓励 </p>
    </div>
</div>
        
        <br />
        <div id="comment-container">
        </div>
        <div id="disqus_thread"></div>
        <div id="lv-container"></div>
        <div class="giscus"></div>
    </div>
</div>

    </div>
</div>


<footer class="footer">
    <ul class="list-inline text-center">
        
        

        
        <li>
            <a target="_blank" href="http://weibo.com/zha0ku1">
                            <span class="fa-stack fa-lg">
                                  <i class="iconfont icon-weibo"></i>
                            </span>
            </a>
        </li>
        

        

        
        <li>
            <a target="_blank"  href="https://github.com/zkool">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-github"></i>
                            </span>
            </a>
        </li>
        

        

    </ul>
    
    <p>
        <span>/</span>
        
        <span><a target="_blank" rel="noopener" href="https://niexiaotao.com">Xiaotao&#39;s Page</a></span>
        <span>/</span>
        
        <span><a href="#">It helps SEO</a></span>
        <span>/</span>
        
    </p>
    
    <p>
        <span id="busuanzi_container_site_pv">
            <span id="busuanzi_value_site_pv"></span>PV
        </span>
        <span id="busuanzi_container_site_uv">
            <span id="busuanzi_value_site_uv"></span>UV
        </span>
        Created By <a target="_blank" rel="noopener" href="https://hexo.io/">Hexo</a>  Theme <a target="_blank" rel="noopener" href="https://github.com/aircloud/hexo-theme-aircloud">AirCloud</a></p>
</footer>




</body>

<script>
    // We expose some of the variables needed by the front end
    window.hexo_search_path = "search.json"
    window.hexo_root = "/"
    window.isPost = true
</script>
<script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>

<script src="/js/index.js"></script>

<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>






</html>
